package com.dao.lzj;

import com.bean.lzj.Material;
import com.bean.lzj.TruckSpace;
import com.util.lzj.DBUtil;

import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.RequestMapping;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
@Repository

public class MaterielDao {

    /**
     * 查询所以物料信息表数据
     * @return
     * @throws SQLException
     */
    public static List<Material> getcarl() throws SQLException {
        List<Material> wlList = new ArrayList<>();
        String sql = "select * from mattered";
        DBUtil dbUtil = new DBUtil();
        PreparedStatement ps = dbUtil.getPreparedStatement(sql);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            Material wlxx=new Material();
            wlxx.setMid(rs.getInt("mid"));
            wlxx.setMname(rs.getString("mname"));
            wlxx.setOrigin(rs.getString("origin"));
            wlxx.setStock(rs.getString("stock"));
            wlxx.setMprice(rs.getString("mprice"));
            wlList.add(wlxx);
        }
        return wlList;
    }

    /**
     * 综合查询
     */


    public static List<Material> getwlzhxx(String cxwlmc, String cxwlcd) throws SQLException {
        String sql = "select * from mattered where 1=1 ";
        if (cxwlmc != null && !"".equals(cxwlmc)) {
            sql = sql + " and mname ='" + cxwlmc + "'";
            System.out.println(1);
        }
        if (cxwlcd != null && !"".equals(cxwlcd)) {
            sql = sql + " and origin ='" + cxwlcd + "'";

        }
        DBUtil dbUtil = new DBUtil();
        List<Material> wlList = new ArrayList<>();
        System.out.println(sql);

        PreparedStatement ps = dbUtil.getPreparedStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Material wlxx=new Material();
            wlxx.setMid(rs.getInt("mid"));
            wlxx.setMname(rs.getString("mname"));
            wlxx.setOrigin(rs.getString("origin"));
            wlxx.setStock(rs.getString("stock"));
            wlxx.setMprice(rs.getString("mprice"));
            wlList.add(wlxx);
        }
        return wlList;
    }

    /**
     * 添加物料信息
     * @param wlmc
     * @param wlcd
     * @param wlkc
     * @param wljg
     * @return
     */
    public static boolean tianjiawl(String wlmc, String wlcd, int wlkc, int wljg) {
        DBUtil dbutil = new DBUtil();
        String sql = "INSERT INTO `project2`.`mattered`(`mid`, `mname`, `origin`, `stock`, `mprice`) VALUES (null, ?, ?, ?, ?)";
        PreparedStatement ps = dbutil.getPreparedStatement(sql);
        try {
            ps.setString(1, wlmc);
            ps.setString(2, wlcd);
            ps.setInt(3,wlkc);
            ps.setInt(4,wljg);



            int result = ps.executeUpdate();
            if (result == 1) {
                System.out.println("添加成功");
                return true;

            } else {
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    public static boolean deleteWlById(int mid) {

        DBUtil dbutil = new DBUtil();
        String sql = "delete from mattered where mid = ?";
        PreparedStatement ps = dbutil.getPreparedStatement(sql);
        try {
            ps.setInt(1, mid);
            int result = ps.executeUpdate();
            if (result == 1) {
                System.out.println("删除成功");
                return true;
            } else {
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 批量删除物料信息
     * @param fxk
     * @return
     */
    public static boolean deleteinbatchesById(int fxk) throws SQLException {
        System.out.println("-----------");
        int a = fxk;
        int stock=0;
        System.out.println(fxk);
        // String rs[] = request.getParameterValues("fxk");

        DBUtil dbutil = new DBUtil();
        String sql = "select * from mattered where mid = ? ";

        PreparedStatement ps = dbutil.getPreparedStatement(sql);
        ps.setInt(1, Integer.parseInt(String.valueOf(a)));
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Material wlxx=new Material();
            wlxx.setMid(rs.getInt("mid"));
            wlxx.setMname(rs.getString("mname"));
            wlxx.setOrigin(rs.getString("origin"));
            wlxx.setStock(rs.getString("stock"));
            wlxx.setMprice(rs.getString("mprice"));
            stock=rs.getInt("stock");
        }
        System.out.println(stock);
        if (stock<=0){
            String sql1 = "delete from mattered where mid = ?";
            ps = dbutil.getPreparedStatement(sql1);
            ps.setInt(1, Integer.parseInt(String.valueOf(a)));
            int result = ps.executeUpdate();
            if (result == 1) {
                System.out.println("删除成功");
                return true;

            } else {
                return false;
            }


        }else {
            System.out.println("删除失败");
            return false;
        }

    }

    public static boolean getxiugaiwuliao(int mid, String xgwlmc, String xgwlcd, int xgwlkc, int xgwljg) {
        DBUtil dbutil = new DBUtil();

        String sql="UPDATE `project2`.`mattered` SET `mname` = ?, `origin` = ?, `stock` = ?, `mprice` = ? WHERE `mid` = ?";
        PreparedStatement ps = dbutil.getPreparedStatement(sql);
        try {
            ps.setString(1, xgwlmc);
            ps.setString(2, xgwlcd);
            ps.setInt(3,xgwlkc);
            ps.setInt(4,xgwljg);
            ps.setInt(5, (mid));



            int result = ps.executeUpdate();
            if (result == 1) {
                System.out.println("修改成功");
                return true;

            } else {
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static List<Material> getwlxg(int mid) throws SQLException {
        List<Material> wlList = new ArrayList<>();
        String sql = "select * from mattered where mid=? ";
        DBUtil dbutil = new DBUtil();
        PreparedStatement ps = dbutil.getPreparedStatement(sql);
        ps.setInt(1, mid);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Material wlxx=new Material();
            wlxx.setMid(rs.getInt("mid"));
            wlxx.setMname(rs.getString("mname"));
            wlxx.setOrigin(rs.getString("origin"));
            wlxx.setStock(rs.getString("stock"));
            wlxx.setMprice(rs.getString("mprice"));
            wlList.add(wlxx);
        }
        return wlList;
    }
}
